﻿using System;
using System.Collections.Generic;

using System.Text;
using System.Data;

namespace DBManager.Admin
{
    public class ProductsDAL
    {
        public static DataTable getAllZone()
        {
            string sql = "select * from ZoneProduct where ParentId = 0";
            return DBUtil.SelectTable(sql);
        }
        public static DataTable getAllZoneByParentId(string ParentId)
        {
            string sql = "select * from ZoneProduct where ParentId =" + comm.to_sqlnumber(ParentId);
            return DBUtil.SelectTable(sql);
        }
        public static DataTable getInfomationZoneByID(string ZoneId)
        {
            string sql = "select * from ZoneProduct where ZoneId =" + comm.to_sqlnumber(ZoneId);
            return DBUtil.SelectTable(sql);
        }
        public static string addZone(string title, string url, string ParentId, string status)
        {
            string kq = "0";
            string sql = @"INSERT INTO ZoneProduct(title,url,ParentId,status)
                           VALUES 
                           (" + comm.to_sqltext(title) + @"
                           ," + comm.to_sqltext(url) + @"
                           ," + comm.to_sqlnumber(ParentId) + @"
                           ," + comm.to_sqlnumber(status) + @")";
            try
            {
                DBUtil.Execute(sql);
                kq = "1";
            }
            catch (Exception)
            {
                kq = "0";
            }
            return kq;
        }
        public static string editZone(string title, string url, string ParentId, string status, string zoneid)
        {
            string kq = "0";
            string sql = @"UPDATE ZoneProduct
                           SET title = " + comm.to_sqltext(title) + @"
                          ,url = " + comm.to_sqltext(url) + @"
                          ,ParentId = " + comm.to_sqlnumber(ParentId) + @"
                          ,status = " + comm.to_sqlnumber(status) + @"
                           WHERE zoneid=" + comm.to_sqlnumber(zoneid);
            try
            {
                DBUtil.Execute(sql);
                kq = "1";
            }
            catch (Exception)
            {
                kq = "0";
            }
            return kq;
        }
        //products
        public static DataTable getAllProducts(string sFind, string sZoneID)
        {
            string dk = "";
            string dkZone = "";
            if (sFind != "")
            {
                dk = " and title like '%" + sFind + "%' or sapo like '%" + sFind + "%' ";
            }
            if (sZoneID != "0")
            {
                dkZone = " and (zoneid=" + comm.to_sqlnumber(sZoneID) + " or ZoneIdOther like '%" + sZoneID + "," + "%' )";
            }
            string sql = "select price,sale,LastModifiedDate,DistributionDate,id,case status when 0 then N'Không Dùng' when 1 then N'Dùng' end as trangthai,title,Avatar from Products where 0=0 " + dk + dkZone + " order by id desc";
            return DBUtil.SelectTable(sql);
        }
        public static DataTable GetInfomationProduct(string newsID)
        {
            string sql = "select * from Products where id=" + comm.to_sqlnumber(newsID);
            return DBUtil.SelectTable(sql);
        }
        public static string addProduct(string ZoneId, string ZoneIdOther, string Title, string Sapo, string Avatar, string Author, string IsFocus, string Url, string Tags, string status, string longContent,string price,string sale)
        {
            //them moi san pham
            string kq = "0";
            try
            {
                string sql = @"INSERT INTO Products
                       (
                       ZoneId
                       ,ZoneIdOther
                       ,Title
                       ,Sapo
                       ,Avatar
                       ,Author
                       ,IsFocus
                       ,DistributionDate
                       ,Url
                       ,Tags
                       ,status
                       ,LastModifiedDate
                       ,price
                       ,sale
                       ,longContent)
                        VALUES
                       (
                       " + comm.to_sqlnumber(ZoneId) + @"
                       ," + comm.to_sqltext(ZoneIdOther) + @"
                       ," + comm.to_sqltext(Title) + @"
                       ," + comm.to_sqltext(Sapo) + @"
                       ," + comm.to_sqltext(Avatar) + @"
                       ," + comm.to_sqltext(Author) + @"
                       ," + comm.to_sqlnumber(IsFocus) + @"
                       ,getdate()
                       ," + comm.to_sqltext(Url) + @"
                       ," + comm.to_sqltext(Tags) + @"
                       ," + comm.to_sqltext(status) + @"
                       ,getdate()
                       ," + comm.to_sqlnumber(price) + @"
                       ," + comm.to_sqlnumber(sale) + @"
                       ," + comm.to_sqltext(longContent) + ")";
                DBUtil.Execute(sql);
                kq = "1";
            }
            catch (Exception)
            {
                kq = "0";
            }
            return kq;
        }
        public static string EditProduct(string ID, string ZoneId, string ZoneIdOther, string Title, string Sapo, string Avatar, string Author, string IsFocus, string Url, string Tags, string status, string longContent, string price, string sale)
        {
            //sua san pham
            string kq = "0";
            try
            {
                string sql = @"UPDATE Products
                           SET
                           ZoneId = " + comm.to_sqlnumber(ZoneId) + @"
                          ,ZoneIdOther = " + comm.to_sqltext(ZoneIdOther) + @"
                          ,Title = " + comm.to_sqltext(Title) + @"
                          ,Sapo = " + comm.to_sqltext(Sapo) + @"
                          ,Avatar = " + comm.to_sqltext(Avatar) + @"
                          ,Author = " + comm.to_sqltext(Author) + @"
                          ,IsFocus = " + comm.to_sqlnumber(IsFocus) + @"
                          ,Url = " + comm.to_sqltext(Url) + @"
                          ,Tags = " + comm.to_sqltext(Tags) + @"
                          ,status = " + comm.to_sqlnumber(status) + @"
                          ,price = " + comm.to_sqlnumber(price) + @"
                          ,sale = " + comm.to_sqlnumber(sale) + @"
                          ,LastModifiedDate = getdate()
                          ,longContent = " + comm.to_sqltext(longContent) + @"
                          WHERE id=" + comm.to_sqlnumber(ID);
                DBUtil.Execute(sql);
                kq = "1";
            }
            catch (Exception)
            {
                kq = "0";
            }
            return kq;
        }

    }
}
